package com.itheima.jdbcdemo.dao;

import com.itheima.jdbcdemo.domain.Student;
import com.itheima.jdbcdemo.utils.JDBCUtils;


import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;


public class StudentDaoImpl implements StudentDao {

    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();

            //执行的sql语句
            String sql = "SELECT * FROM student";
            //获取执行者对象
            stat = con.prepareStatement(sql);

            //接收结果
            rs = stat.executeQuery();
            //处理结果
            while (rs.next()){
                int sid = rs.getInt("sid");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                Student student = new Student(sid,name,age,birthday);
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,stat,rs);
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Student student = null;
        ResultSet rs = null;
        PreparedStatement stat = null;
        Connection con = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "SELECT * FROM student where sid=?";
            stat = con.prepareStatement(sql);
            stat.setInt(1,id);
            rs = stat.executeQuery();
            rs.next();
            //int id1 = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            Date birthday = rs.getDate("birthday");
            student = new Student(id,name,age,birthday);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,stat,rs);
        }
        return student;
    }

    @Override

    public int insert(Student student) {

        Connection con = null;
        PreparedStatement stat = null;
        int rs = 0;
        try {
            con = JDBCUtils.getConnection();
            Integer sid = student.getSid();
            String name = student.getName();
            Integer age = student.getAge();
            Date birthday = student.getBirthday();
            String date = new SimpleDateFormat("yyyy-MM-dd").format(birthday);
            String sql = "INSERT INTO student VALUES (null ,?,?,?);";
            stat = con.prepareStatement(sql);
            stat.setString(1,name);
            stat.setInt(2,age);
            stat.setString(3,date);
            rs = stat.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,stat);
        }

        return rs;
    }

    @Override
    public int update(Student student) {
        Connection con = null;
        PreparedStatement pst = null;
        int rs = 0;
        try {
            con = JDBCUtils.getConnection();
            Integer sid = student.getSid();
            String name = student.getName();
            Integer age = student.getAge();
            Date date = student.getBirthday();
            String birthday = new SimpleDateFormat("yyyy-MM-dd").format(date);
            String sql = "update student SET name=?,age=?,birthday=? where sid = ?";
            pst = con.prepareStatement(sql);
            pst.setString(1,name);
            pst.setInt(2,age);
            pst.setString(3,birthday);
            pst.setInt(4,sid);
            rs = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,pst);
        }
        return rs;
    }

    @Override
    public int delete(Integer id) {
        int i = 0;
        PreparedStatement pst = null;
        Connection con = null;
        try {
            con = JDBCUtils.getConnection();
            String sql = "DELETE FROM student where sid = ?";
            pst = con.prepareStatement(sql);
            pst.setInt(1,id);
            i = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(con,pst);
        }
        return i;
    }
}
